clear all
cap log close
set more off


*************************************************
*************************************************
//// CREATE RAW CITATIONS DATA FOR GEOCODING ////
*************************************************
*************************************************


//// NOTE: READ IN YEAR BY YEAR: LOOP OVER YEARS OF DATA ////


// INITIALIZE LOOP //
forval y=2005/2018 {


// READ IN DATA FOR YEAR Y //
import delimited using ~/FLORIDA/UTC/utc_data_`y'/citation_`y'.txt, clear delim("|")


// RENAME VARIABLES //
// NOTE: COMMENTED OUT VARIABLES WILL BE DROPPED //
rename v1  CitationNumber
// rename v2  CheckDigit
rename v3  CountyNum
rename v4  JurisNumber
// rename v5  CityName
rename v6  IssueAgencyType
// rename v7  IssueAgencyCode
// rename v8  IssueAgencyName
// rename v9  DOTW   // Note: stata can get this from date //
rename v10 OffenseDate
rename v11 OffenseTime
rename v12 OffenseTimeAMPM
rename v13 DriverFirstName
// rename v14 DriverMiddleName
rename v15 DriverLastName
// rename v16 DriverSuffix
// rename v17 HomeAddress
// rename v18 AddDiffLicense
// rename v19 HomeCity
// rename v20 HomeState
rename v21 ZipCode
// rename v22 Telephone
rename v23 BirthDate
rename v24 Race
rename v25 Sex
// rename v26 Height
rename v27 DLnumber
rename v28 DLstate
rename v29 DLclass
// rename v30 DLExpYear
rename v31 CommVehicleCode
rename v32 VehicleYear
rename v33 VehicleMake
rename v34 VehicleStyle
// rename v35 VehicleColor
// rename v36 HazardousMaterials
// rename v37 VehicleTagNum
// rename v38 VehicleTrailerTagNum
rename v39 VehicleState
// rename v40 VehicleTagExpirationYear
rename v41 CompanionCitation
// rename v42 ViolationLocation
// rename v43 DistanceFeet
// rename v44 DistanceMiles
// rename v45 DirectionN
// rename v46 DirectionS
// rename v47 DirectionE
// rename v48 DirectionW
// rename v49 OfNode
rename v50 ActualSpeed
rename v51 PostedSpeed
rename v52 Hwy4Lane
rename v53 HwyInterstate
rename v54 Viol_Careless
rename v55 Viol_Device
rename v56 Viol_Row
rename v57 Viol_Lane
rename v58 Viol_Passing
rename v59 Viol_ChildRestraint
rename v60 Viol_DUI
// rename v61 BloodAlcohol
rename v62 Viol_SeatBelt
rename v63 Viol_Equipment
rename v64 Viol_TagLess
rename v65 Viol_TagMore
rename v66 Viol_Insurance
rename v67 Viol_ExpireDriverLicense
rename v68 Viol_ExpireDriverLicenseMore
rename v69 Viol_InvalidDriverLicense
rename v70 Viol_SuspendedLicense
// rename v71 OtherComments
rename v72 Viol_Code
// rename v73 Filler
// rename v74 StateStatuteInd
// rename v75 Section
// rename v76 Sub_Section
rename v77 Crash
rename v78 PropertyDamage
// rename v79 PropertyDamageAmt
rename v80 Injury
rename v81 SeriousInjury
rename v82 FatalInjury
rename v83 ArrestMethod
// rename v84 CrimViolCourtAppReq
// rename v85 InfractCourtApp
// rename v86 InfractCourtAppNot
// rename v87 CourtDate
// rename v88 CourtTime
// rename v89 CourtName
// rename v90 CourtTimeAMPM
// rename v91 CourtAddress
// rename v92 CourtCity
// rename v93 CourtState
// rename v94 CourtZip
// rename v95 ArrestDeliveredTo
// rename v96 ArrestDeliveredDate
// rename v97 OfficerRank
rename v98 OfficerFirstName
rename v99 OfficerMiddleName
rename v100 OfficerLastName
// rename v101 OfficerBadgeNum
rename v102 OfficerID
// rename v103 OfficerUnit
rename v104 BAL08
// rename v105 DUI_Refuse
// rename v106 DUI_LicenseSurrender
// rename v107 DUI_License_RSN
// rename v108 DUI_Eligible_Y
// rename v109 DUI_Eligible_RSN
// rename v110 DUI_BAR_Office
// rename v111 Status
// rename v112 AggressiveDriverFlag
rename v113 CriminalIndicator
rename v114 FineAmount
// rename v115 FormVersion
// rename v116 IssueArrestDate
// rename v117 OfficerDeliveryVerification
// rename v118 DueDate
// rename v119 Motorcycle
// rename v120 PassengerVehicle16
// rename v121 OfficerReExamFlag
// rename v122 DUIViol_Under18
rename v123 EcitationInd
// rename v124 NameChange
rename v125 CommercialDL
rename v126 GPSLat
rename v127 GPSLong
rename v128 Viol_SignalRed
rename v129 Viol_WorkersPres
rename v130 Viol_HandHeld
rename v131 Viol_SchoolZone
// rename v132 AgencyID
// rename v133 PermReg
// rename v134 SpeedDeviceID
// rename v135 DLSeize
// rename v136 Business
// rename v137 Filler2

// DROP VARIABLES THAT ARE NOT RENAMED //
cap drop v*

// TRIM CITATION //
replace CitationNumber = trim(upper(CitationNumber))
keep CitationNumber CountyNum GPSLat GPSLong

cap destring GPSLat, replace force
cap destring GPSLong, replace force
keep if !mi(GPSLat)&!mi(GPSLong)

qui compress
saveold TEMP_`y', replace

}


use TEMP_2005, clear
forval y=2006/2018 {
qui append using TEMP_`y'
}

qui compress
saveold utc_togeo_05_18, replace
export delimited utc_togeo_05_18.csv, delim(",")


forval y=2005/2018 {
rm TEMP_`y'.dta
}














